<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <meta name="google-site-verification" content="k9iQUbEI9rWq3xYeh63ATztKdkthC4dNRHV_25maJ3Q" />
  <title>数据库范式的思考 | Taylor&#39;s Learning Diary</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="还记得第一本数据库教程《数据库系统概论》，从最初的范式讲起，只记得当时学的还模模糊糊，今天又看到类似的文章【从范式谈起-余晟】，粗略总结一下数据库范式，也是加深一下记忆吧！在这之前，说下我对关系型数据库管理系统RDBMS和NOSQL的理解：RDBMS主要突出其R(Relation)关系；而NoSQL如：常见的MC、Redis、MongoDB，支持key-value型等简单的数据类型，适用于大数据时">
<meta name="keywords" content="MySQL">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库范式的思考">
<meta property="og:url" content="https://upeng.github.io/blog/2016/06/25/normal/index.html">
<meta property="og:site_name" content="Taylor&#39;s Learning Diary">
<meta property="og:description" content="还记得第一本数据库教程《数据库系统概论》，从最初的范式讲起，只记得当时学的还模模糊糊，今天又看到类似的文章【从范式谈起-余晟】，粗略总结一下数据库范式，也是加深一下记忆吧！在这之前，说下我对关系型数据库管理系统RDBMS和NOSQL的理解：RDBMS主要突出其R(Relation)关系；而NoSQL如：常见的MC、Redis、MongoDB，支持key-value型等简单的数据类型，适用于大数据时">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="http://upeng.github.io/image/table1.png">
<meta property="og:image" content="http://upeng.github.io/image/table2.png">
<meta property="og:updated_time" content="2017-06-28T16:12:44.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="数据库范式的思考">
<meta name="twitter:description" content="还记得第一本数据库教程《数据库系统概论》，从最初的范式讲起，只记得当时学的还模模糊糊，今天又看到类似的文章【从范式谈起-余晟】，粗略总结一下数据库范式，也是加深一下记忆吧！在这之前，说下我对关系型数据库管理系统RDBMS和NOSQL的理解：RDBMS主要突出其R(Relation)关系；而NoSQL如：常见的MC、Redis、MongoDB，支持key-value型等简单的数据类型，适用于大数据时">
<meta name="twitter:image" content="http://upeng.github.io/image/table1.png">
  
    <link rel="alternate" href="/atom.xml" title="Taylor&#39;s Learning Diary" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/blog/css/style.css">
  

</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/blog/" id="logo">Taylor&#39;s Learning Diary</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/blog/">Home</a>
        
          <a class="main-nav-link" href="/blog/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="搜索"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://upeng.github.io/blog"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="database-normal" class="article article-type-database" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/blog/2016/06/25/normal/" class="article-date">
  <time datetime="2016-06-25T08:32:12.000Z" itemprop="datePublished">2016-06-25</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/blog/categories/数据库范式/">数据库范式</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      数据库范式的思考
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>还记得第一本数据库教程《数据库系统概论》，从最初的范式讲起，只记得当时学的还模模糊糊，今天又看到类似的文章【从范式谈起-余晟】，粗略总结一下数据库范式，也是加深一下记忆吧！在这之前，说下我对关系型数据库管理系统RDBMS和NOSQL的理解：RDBMS主要突出其R(Relation)关系；而NoSQL如：常见的MC、Redis、MongoDB，支持key-value型等简单的数据类型，适用于大数据时代的很多场景，为开发提供了巨大便利；使用场景不同，各有千秋。<br><a id="more"></a><br>数据库范式，简单的说就是<strong>规范化，在不丢失数据的情况下，把表拆分成更小、冗余度更小的表，通过不同表之间的外键用链接join方式得到需求数据</strong>，范式NF可以粗略的理解为一张数据表的表结构所符合的某种设计标准的级别，数据库范式也分为1NF，2NF，3NF，BCNF，4NF，5NF。</p>
<p><strong>通常一张表满足了第三范式，就认为这个表是规范化的，增删改查不会出现异常。</strong></p>
<h3 id="第一范式：属性不可拆分"><a href="#第一范式：属性不可拆分" class="headerlink" title="第一范式：属性不可拆分"></a>第一范式：属性不可拆分</h3><p>即表中的任何列（属性）都应当是承载信息的最小单位，不可再分；<br><em>1NF是所有关系型数据库的最基本要求</em></p>
<h3 id="第二范式：主键必须最小"><a href="#第二范式：主键必须最小" class="headerlink" title="第二范式：主键必须最小"></a>第二范式：主键必须最小</h3><p>即：在满足第一范式的基础上，所有非主属性必须完全依赖于主键，而且不能依赖于主键的某个子集<br><img src="http://upeng.github.io/image/table1.png" alt="表格"><br>主键：（学号，课名），系主任通过学号就可以确定；上表不满足2NF</p>
<blockquote>
<p>如何判断是否符合2NF？</p>
</blockquote>
<ul>
<li>第一步：找出数据表中所有的码。</li>
<li>第二步：根据第一步所得到的码，找出所有的主属性。</li>
<li>第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。</li>
<li>第四步：查看是否存在非主属性对码的部分函数依赖。</li>
</ul>
<p><em>2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖</em></p>
<h3 id="第三范式（3NF）：主键必须直接依赖，不能传递依赖"><a href="#第三范式（3NF）：主键必须直接依赖，不能传递依赖" class="headerlink" title="第三范式（3NF）：主键必须直接依赖，不能传递依赖"></a>第三范式（3NF）：主键必须直接依赖，不能传递依赖</h3><p><img src="http://upeng.github.io/image/table2.png" alt="表格"><br>上图表1符合3NF<br>上图表2主键是学号，其他3个是非主属性，学号-&gt;系名，系名-&gt;系主任；存在传递依赖，故表2不满足3NF<br><em>3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖</em>。</p>
<p>更深入的理解：<a href="https://www.zhihu.com/question/24696366" target="_blank" rel="external">知乎</a> ，注：文中图片均来源于此</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://upeng.github.io/blog/2016/06/25/normal/" data-id="cj8x7818f0023gwp41hma1az5" class="article-share-link">Share</a>
      
        <a href="https://upeng.github.io/blog/2016/06/25/normal/#disqus_thread" class="article-comment-link">留言</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/blog/tags/MySQL/">MySQL</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/blog/2016/06/30/zephir/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          使用zephir快速编写php扩展
        
      </div>
    </a>
  
  
    <a href="/blog/2016/06/17/thinking-1/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">In Recent Days</div>
    </a>
  
</nav>

  
</article>


<section id="comments">
  <div id="disqus_thread">
    <noscript>Please enable JavaScript to view the <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
  </div>
</section>
</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Linux/">Linux</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/MySQL/">MySQL</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/PHP/">PHP</a><span class="category-list-count">15</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Thinking/">Thinking</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/其他/">其他</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/前端/">前端</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/数据库范式/">数据库范式</a><span class="category-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">标签云</h3>
    <div class="widget tagcloud">
      <a href="/blog/tags/CI/" style="font-size: 10px;">CI</a> <a href="/blog/tags/JQuery/" style="font-size: 10px;">JQuery</a> <a href="/blog/tags/Mac/" style="font-size: 13.33px;">Mac</a> <a href="/blog/tags/MySQL/" style="font-size: 10px;">MySQL</a> <a href="/blog/tags/awk/" style="font-size: 10px;">awk</a> <a href="/blog/tags/bash/" style="font-size: 10px;">bash</a> <a href="/blog/tags/composer/" style="font-size: 10px;">composer</a> <a href="/blog/tags/eloquent/" style="font-size: 10px;">eloquent</a> <a href="/blog/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/blog/tags/idempotence/" style="font-size: 10px;">idempotence</a> <a href="/blog/tags/item/" style="font-size: 10px;">item</a> <a href="/blog/tags/laravel/" style="font-size: 20px;">laravel</a> <a href="/blog/tags/linux/" style="font-size: 16.67px;">linux</a> <a href="/blog/tags/mac/" style="font-size: 10px;">mac</a> <a href="/blog/tags/memcacheq/" style="font-size: 10px;">memcacheq</a> <a href="/blog/tags/mysql/" style="font-size: 16.67px;">mysql</a> <a href="/blog/tags/nc/" style="font-size: 10px;">nc</a> <a href="/blog/tags/packageist/" style="font-size: 10px;">packageist</a> <a href="/blog/tags/php/" style="font-size: 13.33px;">php</a> <a href="/blog/tags/sed/" style="font-size: 10px;">sed</a> <a href="/blog/tags/shell/" style="font-size: 10px;">shell</a> <a href="/blog/tags/static/" style="font-size: 10px;">static</a> <a href="/blog/tags/thinking/" style="font-size: 13.33px;">thinking</a> <a href="/blog/tags/tmux/" style="font-size: 10px;">tmux</a> <a href="/blog/tags/vagrant/" style="font-size: 10px;">vagrant</a> <a href="/blog/tags/validator/" style="font-size: 10px;">validator</a> <a href="/blog/tags/vim/" style="font-size: 10px;">vim</a> <a href="/blog/tags/vuejs/" style="font-size: 10px;">vuejs</a> <a href="/blog/tags/zephir/" style="font-size: 10px;">zephir</a> <a href="/blog/tags/zsh/" style="font-size: 10px;">zsh</a> <a href="/blog/tags/设计模式/" style="font-size: 13.33px;">设计模式</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/10/">十月 2017</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/09/">九月 2017</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/12/">十二月 2016</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/11/">十一月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/10/">十月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/09/">九月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/08/">八月 2016</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/07/">七月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/06/">六月 2016</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/05/">五月 2016</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/04/">四月 2016</a><span class="archive-list-count">7</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/02/">二月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/01/">一月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/10/">十月 2015</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/08/">八月 2015</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/07/">七月 2015</a><span class="archive-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/blog/2017/10/18/laravel-validator/">Laravel Validator自定义参数验证规则</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/28/linux-sed/">linux常用命令之sed</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/27/shell-script-learning/">shell脚本由点到面学习总结</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/14/laravel-eloquent-index/">Eloquent ORM多个and和or条件查询</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/10/Linux压缩解压缩命令-index/">Linux常用压缩解压缩命令</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2017 Tayloryu<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/blog/" class="mobile-nav-link">Home</a>
  
    <a href="/blog/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    
<script>
  var disqus_shortname = 'tayloryu';
  
  var disqus_url = 'https://upeng.github.io/blog/2016/06/25/normal/';
  
  (function(){
    var dsq = document.createElement('script');
    dsq.type = 'text/javascript';
    dsq.async = true;
    dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
    (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
  })();
</script>


<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/blog/fancybox/jquery.fancybox.css">
  <script src="/blog/fancybox/jquery.fancybox.pack.js"></script>


<script src="/blog/js/script.js"></script>
  </div>
</body>
</html>